package com.css.fxfzdzzh.modules.eqLandslide.lawAnalysis.repository.impl;

import com.css.fxfzdzzh.modules.eqLandslide.lawAnalysis.entity.*;
import com.css.fxfzdzzh.modules.eqLandslide.lawAnalysis.repository.LawAnalysisRepository;
import com.css.fxfzdzzh.modules.eqLandslide.lawAnalysis.vo.BufferAreaVO;
import com.css.fxfzdzzh.modules.tifData.service.ElevationMapService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * @Description: fxfzdzzh
 * @Author: lhl
 * @CreateDate: 2023/2/27 14:57
 */
@Repository
public class LawAnalysisRepositoryImpl implements LawAnalysisRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    ElevationMapService elevationMapService;

    @Override
    public BigDecimal getAreaByGeom(String geomText) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "SELECT st_area(ST_Transform(st_geometryfromtext(?,4490),"+epsg+"))";

        return jdbcTemplate.queryForObject(sql, BigDecimal.class, geomText);
    }

    @Override
    public BigDecimal getAreaDegreeByGeom(String geomText) {
        String sql = "SELECT st_area(st_geometryfromtext(?,4490))";

        return jdbcTemplate.queryForObject(sql, BigDecimal.class, geomText);
    }

    @Override
    public Boolean getSTIntersects(String geomText, BigDecimal longitude, BigDecimal latitude) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490))";

        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, longitude, latitude);
    }

    @Override
    public Boolean getSTIntersects(String geomText, String geomText1) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490))";
        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, geomText1);
    }

    @Override
    public BigDecimal getDistance(String geomText, BigDecimal longitude, BigDecimal latitude) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "SELECT st_length(st_transform(ST_ShortestLine(st_geometryfromtext(?,4490),st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490)), "+epsg+"))";

        return jdbcTemplate.queryForObject(sql, BigDecimal.class, geomText, longitude, latitude);
    }

    @Override
    public BigDecimal getDistance(String geomText, String point) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "SELECT st_length(st_transform(ST_ShortestLine(st_geometryfromtext(?,4490),st_geometryfromtext" +
                "(?,4490)), "+epsg+"))";
        return jdbcTemplate.queryForObject(sql, BigDecimal.class, geomText, point);
    }

    @Override
    public String getStUnion(Object object1, Object object2) {
        String sql = "SELECT St_AsText(ST_union(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490)))";

        return jdbcTemplate.queryForObject(sql, String.class, object1, object2);
    }

    @Override
    public BufferAreaVO getBufferByGeom(String geomText, String geomText1, int number1, int number2) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "select b.geom geom,st_area(st_geomfromtext(b.geom,4490)) as area from (select " +
                "st_astext(st_difference(st_intersection(";
        if (number2 == 30000||number2==2000) {
            sql = sql + "(select st_geometryfromtext(?,4490) as polygon),";
        } else {
            sql = sql + "st_transform(st_buffer(st_transform(a.geom, "+epsg+"),?),4490),";
        }
        sql = sql + " (select st_geometryfromtext(?,4490) as polygon)), st_intersection(st_transform(st_buffer(" +
                "st_transform(a.geom, "+epsg+"),?),4490), (select st_geometryfromtext(?,4490) as polygon)))) as geom " +
                "from (select st_intersection((select st_geometryfromtext(?,4490) as line), " +
                "(select st_geometryfromtext(?,4490) as polygon)) as geom) a) b";
        if (number2 == 30000||number2==2000) {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BufferAreaVO.class), geomText, geomText, number2, geomText, geomText1,
                    geomText);
        } else {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BufferAreaVO.class), number1, geomText, number2, geomText, geomText1,
                    geomText);
        }
    }

    //区域的缓冲区内（断层、水系）数据按距离分段生成缓冲区环
    @Override
    public BufferAreaVO getBufferByGeomBuffer(String bufferGeomText, String geomText, String geomText1, int number1, int number2) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        /*String sql = "select b.geom geom,st_area(st_geomfromtext(b.geom,4490)) as area from (select " +
                "st_astext(st_intersection(st_difference(st_intersection(";
        if (number2 == 30000||number2==2000) {
            sql = sql + "(select st_geometryfromtext(?,4490) as polygon),";
        } else {
            sql = sql + "st_transform(st_buffer(st_transform(a.geom, "+epsg+"),?),4490),";
        }
        sql = sql + " (select st_geometryfromtext(?,4490) as polygon)), st_intersection(st_transform(st_buffer(" +
                "st_transform(a.geom, "+epsg+"),?),4490), (select st_geometryfromtext(?,4490) as polygon))),st_geomfromtext(?,4490))) as geom " +
                "from (select st_intersection((select st_geometryfromtext(?,4490) as line), " +
                "(select st_geometryfromtext(?,4490) as polygon)) as geom) a) b";
        if (number2 == 30000||number2==2000) {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BufferAreaVO.class), bufferGeomText, bufferGeomText, number2, bufferGeomText, geomText, geomText1,
                    bufferGeomText);
        } else {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BufferAreaVO.class), number1, bufferGeomText, number2, bufferGeomText, geomText, geomText1,
                    bufferGeomText);
        }
        */
        //区域的缓冲区内（断层、水系）数据按距离分段生成缓冲区环，并与任务区域相交结果
        //bufferGeomText--任务区域的缓冲区；geomText--任务区域；geomText1--断层、水系数据；number1--外圈缓冲半径；number2--内圈缓冲半径
        String sql = "select b.geom geom,st_area(st_geomfromtext(b.geom,4490)) as area from (select " +
                "st_astext(st_intersection(st_difference(";
        if (number2 == 30000||number2==2000) {//外圈是任务区域
            sql = sql + "(select st_geometryfromtext(?,4490) as polygon),";
        } else {
            sql = sql + "st_transform(st_buffer(st_transform(a.geom, "+epsg+"),?),4490),";
        }
        sql = sql + "st_transform(st_buffer(st_transform(a.geom, "+epsg+"),?),4490)),st_geomfromtext(?,4490))) as geom " +
                "from (select st_intersection((select st_geometryfromtext(?,4490) as line), " +
                "(select st_geometryfromtext(?,4490) as polygon)) as geom) a) b";
        if (number2 == 30000||number2==2000) {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BufferAreaVO.class), geomText, number2, geomText, geomText1, bufferGeomText);
        } else {
            return  jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BufferAreaVO.class), number1, number2, geomText, geomText1, bufferGeomText);
        }
    }

    //生成缓冲区
    @Override
    public String getBufferByGeomMeter(String geomText, int meter) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "select st_astext(st_transform(st_buffer(st_transform(st_geomfromtext(?,4490), "+epsg+"),?),4490))";
        return jdbcTemplate.queryForObject(sql, String.class, geomText, meter);
    }

    @Override
    public String getSTIntersection(String geomText, String geomText1) {
        String sql = "SELECT st_astext(ST_Intersection(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490))) as geom where ST_Intersects" +
                "(st_geometryfromtext(?,4490),st_geometryfromtext(?,4490))";

        return jdbcTemplate.queryForObject(sql, String.class, geomText, geomText1, geomText, geomText1);
    }

    @Override
    public String getSTCentroid(String geomText) {
        String sql = "SELECT ST_AsText(ST_centroid(st_geometryfromtext(?,4490)))";

        return jdbcTemplate.queryForObject(sql, String.class, geomText);
    }

    @Override
    public int save0(DzzhSlideParam entity) {
        String sql = "insert into dzzh_slide_param(id,param1,tab,param2,param3,geom,user_id,param_type)" +
                " values(:id,:param1,:tab,:param2,:param3,st_geometryfromtext(:geom,4490),:userId,:paramType)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int save1(DzzhSlideResultIntensity entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(geomText);

        String sql = "insert into dzzh_slide_result_intensity(dsri_id,intensity,slope_area,slope_density,geom" +
                ",dsp_id)" +
                " values(:dsriId,:intensity,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490)" +
                ",:dspId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int save2(DzzhSlideResultFault entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(geomText);

        String sql = "insert into dzzh_slide_result_fault(dsrf_id,fault_range,slope_area,slope_density,geom,dsp_id)" +
                " values(:dsrfId,:faultRange,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490),:dspId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }


    @Override
    public int save3(DzzhSlideResultTera entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(geomText);

        String sql = "insert into dzzh_slide_result_tera(dsrt_id,tera_range,slope_area,slope_density,geom,dsp_id)" +
                " values(:dsrtId,:teraRange,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490),:dspId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int save4(DzzhSlideResultSlope entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        //String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(entity.getGeomText());

        String sql = "insert into dzzh_slide_result_slope(dsrs_id,slope_range,slope_area,slope_density,geom,dsp_id)" +
                " values(:dsrsId,:slopeRange,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490),:dspId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int save5(DzzhSlideResultAspect entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(geomText);

        String sql = "insert into dzzh_slide_result_aspect(dsra_id,aspect_range,slope_area,slope_density,geom,dsp_id)" +
                " values(:dsraId,:aspectRange,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490),:dspId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int save6(DzzhSlideResultDrainage entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(geomText);

        String sql = "insert into dzzh_slide_result_drainage(dsrd_id,drainage_range,slope_area,slope_density,geom," +
                "dsp_id)" +
                " values(:dsrdId,:drainageRange,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490),:dspId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int save7(DzzhSlideResultLithology entity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = elevationMapService.getGeomValidWkt(entity.getGeomText());
        entity.setGeomText(geomText);

        String sql = "insert into dzzh_slide_result_lithology(dsrl_id,lithology_range,slope_area,slope_density,geom," +
                "dsp_id,lithology_name,lithology_code)" +
                " values(:dsrlId,:lithologyRange,:slopeArea,:slopeDensity,st_geometryfromtext(:geomText,4490),:dspId" +
                ",:lithologyName,:lithologyCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public DzzhSlideParam getDzzhSlideParamByUserId(String userId,String type) {
        List<DzzhSlideParam> query = jdbcTemplate.query("select *,st_astext(geom) geom from dzzh_slide_param where user_id='" + userId + "' and param_type='" + type + "'",
                new BeanPropertyRowMapper<>(DzzhSlideParam.class));
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public int delete0(String userId,String type) {
        return jdbcTemplate.update("delete from dzzh_slide_param where user_id='" + userId + "' and param_type='" + type + "'");
    }

    @Override
    public int delete1(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_intensity where dsp_id='" + dspId + "'");
    }

    @Override
    public int delete2(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_fault where dsp_id='" + dspId + "'");
    }

    @Override
    public int delete3(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_tera where dsp_id='" + dspId + "'");
    }

    @Override
    public int delete4(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_slope where dsp_id='" + dspId + "'");
    }

    @Override
    public int delete5(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_aspect where dsp_id='" + dspId + "'");
    }

    @Override
    public int delete6(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_drainage where dsp_id='" + dspId + "'");
    }

    @Override
    public int delete7(String dspId) {
        return jdbcTemplate.update("delete from dzzh_slide_result_lithology where dsp_id='" + dspId + "'");
    }

    @Override
    public List<DzzhSlideResultIntensity> getByDspId1(String dspId) {
        return jdbcTemplate.query("select *,st_astext(geom) geomtext from dzzh_slide_result_intensity where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultIntensity.class));
    }

    @Override
    public List<DzzhSlideResultFault> getByDspId2(String dspId) {
        return jdbcTemplate.query("select *,st_astext(geom) geomtext from dzzh_slide_result_fault where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultFault.class));
    }

    @Override
    public List<DzzhSlideResultTera> getByDspId3(String dspId) {
        return jdbcTemplate.query("select *,st_astext(geom) geomtext from dzzh_slide_result_tera where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultTera.class));
    }

    @Override
    public List<DzzhSlideResultSlope> getByDspId4(String dspId) {
        return jdbcTemplate.query("select dsrs_id,slope_range,slope_area,slope_density,dsp_id" +
                        " from dzzh_slide_result_slope where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultSlope.class));
    }

    @Override
    public List<DzzhSlideResultAspect> getByDspId5(String dspId) {
        return jdbcTemplate.query("select dsra_id,aspect_range,slope_area,slope_density,dsp_id" +
                        " from dzzh_slide_result_aspect where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultAspect.class));
    }

    @Override
    public List<DzzhSlideResultDrainage> getByDspId6(String dspId) {
        return jdbcTemplate.query("select *,st_astext(geom) geomtext from dzzh_slide_result_drainage where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultDrainage.class));
    }

    @Override
    public List<DzzhSlideResultLithology> getByDspId7(String dspId) {
        return jdbcTemplate.query("select *,st_astext(geom) geomtext from dzzh_slide_result_lithology where dsp_id='"+dspId+"'",
                new BeanPropertyRowMapper<>(DzzhSlideResultLithology.class));
    }

    @Override
    public List<Map<String,Object>> getByTableName(String tableName){
        String sql="select gridvalue,st_asText(smgeometry) smgeometry from "+tableName;
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public int dropTable(String tableName){
        return jdbcTemplate.update("drop table "+tableName);
    }

}
